package com.aofeng.hybrid.service;

import android.content.Context;

import com.aofeng.hybrid.orm.JSonMapConverter;
import com.aofeng.hybrid.orm.SqlHelper;
import com.aofeng.hybrid.util.CommUtil;
import com.aofeng.hybrid.util.ExpressionHelper;

import org.json.JSONArray;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.Iterator;
import java.util.Map;

/**
 * Sql服务
 * Created by LGY on 2016-07-04.
 */
public class SqlService extends GenericService {
    private static final Logger log = LoggerFactory.getLogger(SqlService.class);

    public  SqlService(Context context) {
        super(context);
        services.put("sql", this);
    }

    /**
     * sql 服务入口
     * @param json 格式: {alias:alias, criteria:{}, pageNo:1, pageSize:40}
     * @return
     * @throws Exception
     */
    @Override
    public JSONObject action(JSONObject json){
        JSONObject result = new JSONObject();
        try {
            result.put("code", 500);
            result.put("data", "不可处理异常。");
            if(json.has("alias")) {
                JSONObject sumJSON = getSumAndCnt(json.getString("alias"), json);
                JSONObject data = new JSONObject();
                data.put("sum", sumJSON);
                log.debug("得到行数：" + sumJSON);
                if (json.has("pageNo")) {
                    JSONArray array = find(json.getString("alias"), json.getJSONObject("criteria"), json.getInt("pageNo"), json.getInt("pageSize"));
                    result.put("code", 200);
                    data.put("rows", array);
                    result.put("data", data);
                    log.debug("得到数据" + data.toString());
                    return result;
                } else {
                    JSONArray array = find(json.getString("alias"), json.getJSONObject("criteria"), -1, -1);
                    result.put("code", 200);
                    data.put("rows", array);
                    result.put("data", data);
                    log.debug("得到数据" + data.toString());
                    return result;
                }
            }
            else {
                JSONArray rows = SqlHelper.list(context, json.getString("sql"));
                result.put("code", 200);
                result.put("data", rows);
                return result;
            }
        } catch(Exception e) {
            try {
                result.put("code", 500);
                result.put("data", e.getMessage());
                return result;
            } catch (Exception ex) {
                //suck
                return result;
            }
        }
    }

    /**
     * 业务模块执行sql用
     * @param sql
     */
    public void run(String sql) throws Exception {
        SqlHelper.run(context, sql);
    }

    /**
     * 得到计数和汇总
     * @param sqlModuleName sql模块别名
     * @param jsonCriteria 查询条件
     * @return 计数结果
     */
    public JSONObject getSumAndCnt(String sqlModuleName, JSONObject jsonCriteria) throws Exception {
        JSONObject param = jsonCriteria.getJSONObject("criteria");
        Map<String, Object> params = JSonMapConverter.toMap(param);
        String sql = call(sqlModuleName, params);
        JSONArray sums = null;
        if (jsonCriteria.has("sums")) {
            sums = jsonCriteria.getJSONArray("sums");
        }
        sql = attachSumsOnSql(sql, sums);
        return SqlHelper.singleRow(context,sql);
    }

    /**
     *
     * @param sqlModuleName sql模块名
     * @param jsonCriteria  条件
     * @param pageNo         页号
     * @param pageSize       页行数
     * @return  查询结果
     * @throws Exception 异常
     */
    public JSONArray find(String sqlModuleName, JSONObject jsonCriteria, int pageNo, int pageSize) throws Exception {
        Map<String, Object> params = JSonMapConverter.toMap(jsonCriteria);
        String sql = call(sqlModuleName, params).replace("\n", "").replace("\t","");
        return SqlHelper.pagedList(context, sql, pageNo, pageSize);
    }

     /**
     * 附加汇总到sum，Sqlite不需要去除order by
     * @param rawSql 原始sql
     * @param sums 汇总项
     * @return 修改过的sql
     */
    private String attachSumsOnSql(String rawSql, JSONArray sums) throws Exception {
        String sql = "select ";
        // 如果有求和部分，产生求和部分的语句
        if (sums != null) {
            for (int i = 0; i < sums.length(); i++) {
                String name = (String) sums.get(i);
                sql += "sum(" + name + ") " + name + ", ";
            }
        }
        sql += "count(*) n from ( "
                + rawSql + ") ___t___";
        return sql;
    }

    /**
     * 处理sql文件，转换成真实sql
     * @param alias 别名
     * @param params 条件
     * @return 编译后的sql
     */
    public String call(String alias, Map<String, Object> params) {
        // 获取原始sql语句
        String path = SqlMapper.getSql(context,alias);
        String sql = CommUtil.readFile(CommUtil.getFilePath(context) + "sqls/" + path);

        // 获取编译后的sql语句
        sql = "$" + sql;
        Iterator<String> itr = services.keySet().iterator();
        //注入服务
        while(itr.hasNext()) {
            String key = itr.next();
            params.put(key, services.get(key));
        }
        sql = ExpressionHelper.run(sql, params).toString();

        return sql;
    }

}
